package interfaceDAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;


import classes.SessionFormation;


public class SessionFormationJDBCDAO implements SessionFormationDAOInterface {

	private DataSource ds;
	
	public SessionFormationJDBCDAO(){
		try{
			
		Context ctx = new InitialContext() ;
		ds = (DataSource) ctx.lookup("java:comp/env/jdbc/video");
		}
		catch(Exception e){
			System.out.println("exception constructeur DAO : " + e);
		}
	}

	@Override
	public ArrayList<SessionFormation> getSessionFormationsList() {
		ArrayList<SessionFormation>sessionsFormationList = new ArrayList<SessionFormation>();
		Connection con = null;
		
		try{
		    con = ds.getConnection();		
		    PreparedStatement pstmt=con.prepareStatement("SELECT ID,DEBUT,FIN,CODE_FORMATION,ID_LIEU FROM SESSION_FORMATION");
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()){
				sessionsFormationList.add(new SessionFormation(rs.getLong(1),rs.getDate(2),rs.getDate(3), rs.getString(4),rs.getLong(5)));
				}
			}
			catch(Exception e){
				System.out.println(e);
			}
			finally{
				if (con != null){
					
					try{
						con.close();
					}
					catch(Exception e)	{
						System.out.println(e);
					}
				}
			}		
		return sessionsFormationList;
	}	
	
	@Override
	public SessionFormation getById(long id) {
		Connection con = null;
		SessionFormation sessionformation = null;

		try{
	    con = ds.getConnection();		
	    PreparedStatement pstmt=con.prepareStatement("SELECT DEBUT,FIN,CODE_FORMATION,ID_LIEU FROM SESSION_FORMATION WHERE ID=?");
		pstmt.setLong(1, id);
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()){
				sessionformation = new SessionFormation(id, rs.getDate(1),rs.getDate(2),rs.getString(3),rs.getLong(4));
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		finally{
			if (con != null){
				
				try{
					con.close();
				}
				catch(Exception e)	{
					System.out.println(e);
				}
			}
		}					
		return sessionformation;
	}
	
	@Override
	public ArrayList<SessionFormation> getByDateDebut(Date date) {
		Connection con = null;
		ArrayList<SessionFormation> sessionformationList =  new ArrayList<SessionFormation>();
		try{
	    con = ds.getConnection();		
	    PreparedStatement pstmt=con.prepareStatement("SELECT ID,FIN,CODE_FORMATION,ID_LIEU FROM SESSION_FORMATION WHERE DEBUT=?");
		pstmt.setDate(1, date);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()){
			sessionformationList.add(new SessionFormation(rs.getLong(1), date,rs.getDate(2),rs.getString(3),rs.getLong(4)));
			}
		}	
		catch(Exception e){
			System.out.println(e);
		}
		finally{
			if (con != null){
				
				try{
					con.close();
				}
				catch(Exception e)	{
					System.out.println(e);
				}
			}
		}					
		return sessionformationList;
	}
	
	@Override
	public ArrayList<SessionFormation> getByLieu(long id) {
		Connection con = null;
		ArrayList<SessionFormation> sessionformationList =  new ArrayList<SessionFormation>();
		try{
	    con = ds.getConnection();		
	    PreparedStatement pstmt=con.prepareStatement("SELECT ID,DEBUT,FIN,CODE_FORMATION FROM SESSION_FORMATION WHERE ID_LIEU=?");
		pstmt.setLong(1, id);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()){
			sessionformationList.add(new SessionFormation(rs.getLong(1), rs.getDate(2),rs.getDate(3),rs.getString(4),id));
			}
		}	
		catch(Exception e){
			System.out.println(e);
		}
		finally{
			if (con != null){
				
				try{
					con.close();
				}
				catch(Exception e)	{
					System.out.println(e);
				}
			}
		}					
		return sessionformationList;
	}
	@Override
	public ArrayList<SessionFormation> getByLieuEtDateDebut(long id, Date date){
	
		Connection con = null;
		ArrayList<SessionFormation> sessionformationList =  new ArrayList<SessionFormation>();
		try{
	    con = ds.getConnection();		
	    PreparedStatement pstmt=con.prepareStatement("SELECT ID,FIN,CODE_FORMATION FROM SESSION_FORMATION WHERE ID_LIEU=? AND DEBUT=?");
		pstmt.setLong(1, id);
		pstmt.setDate(2, date);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()){
			sessionformationList.add(new SessionFormation(rs.getLong(1),date, rs.getDate(2),rs.getString(3),id));
			}
		}	
		catch(Exception e){
			System.out.println(e);
		}
		finally{
			if (con != null){
				
				try{
					con.close();
				}
				catch(Exception e)	{
					System.out.println(e);
				}
			}
		}					
		return sessionformationList;
	}
	@Override
	public ArrayList<SessionFormation> getByLieuEtDateDebutEtFormation(long id, Date date, String code){
		
		Connection con = null;
		ArrayList<SessionFormation> sessionformationList =  new ArrayList<SessionFormation>();
		try{
	    con = ds.getConnection();		
	    PreparedStatement pstmt=con.prepareStatement("SELECT ID,FIN FROM SESSION_FORMATION WHERE ID_LIEU=? AND DEBUT=? AND CODE_FORMATION = ?");
		pstmt.setLong(1, id);
		pstmt.setDate(2, date);
		pstmt.setString(3, code);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()){
			sessionformationList.add(new SessionFormation(rs.getLong(1),date, rs.getDate(2),code,id));
			}
		}	
		catch(Exception e){
			System.out.println(e);
		}
		finally{
			if (con != null){
				
				try{
					con.close();
				}
				catch(Exception e)	{
					System.out.println(e);
				}
			}
		}					
		return sessionformationList;
	}
	@Override
	public ArrayList<SessionFormation> getByLieuEtFormation(long id, String code){
		
		Connection con = null;
		ArrayList<SessionFormation> sessionformationList =  new ArrayList<SessionFormation>();
		try{
	    con = ds.getConnection();		
	    PreparedStatement pstmt=con.prepareStatement("SELECT ID,FIN,DEBUT FROM SESSION_FORMATION WHERE ID_LIEU=? AND CODE_FORMATION=?");
		pstmt.setLong(1, id);
		pstmt.setString(2, code);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()){
			sessionformationList.add(new SessionFormation(rs.getLong(1), rs.getDate(2), rs.getDate(3),code,id));
			}
		}	
		catch(Exception e){
			System.out.println(e);
		}
		finally{
			if (con != null){
				
				try{
					con.close();
				}
				catch(Exception e)	{
					System.out.println(e);
				}
			}
		}					
		return sessionformationList;
	}
	@Override
	public ArrayList<SessionFormation> getByDateDebutEtFormation(String code, Date date){
		
		Connection con = null;
		ArrayList<SessionFormation> sessionformationList =  new ArrayList<SessionFormation>();
		try{
	    con = ds.getConnection();		
	    PreparedStatement pstmt=con.prepareStatement("SELECT ID,FIN,ID_LIEU FROM SESSION_FORMATION WHERE DEBUT=? AND CODE_FORMATION=?");
		pstmt.setString(2, code);
		pstmt.setDate(1, date);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()){
			sessionformationList.add(new SessionFormation(rs.getLong(1),date,rs.getDate(2),code,rs.getLong(3)));
			}
		}	
		catch(Exception e){
			System.out.println(e);
		}
		finally{
			if (con != null){
				
				try{
					con.close();
				}
				catch(Exception e)	{
					System.out.println(e);
				}
			}
		}					
		return sessionformationList;
	}
	
	@Override
	public ArrayList<SessionFormation> getByFormation(String code) {
		Connection con = null;
		ArrayList<SessionFormation> sessionformationList =  new ArrayList<SessionFormation>();
		try{
	    con = ds.getConnection();		
	    PreparedStatement pstmt=con.prepareStatement("SELECT ID,DEBUT,FIN,ID_LIEU FROM SESSION_FORMATION WHERE CODE_FORMATION=?");
		pstmt.setString(1, code);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()){
			sessionformationList.add(new SessionFormation(rs.getLong(1),rs.getDate(2),rs.getDate(3),code,rs.getLong(4)));
			}
		}	
		catch(Exception e){
			System.out.println(e);
		}
		finally{
			if (con != null){
				
				try{
					con.close();
				}
				catch(Exception e)	{
					System.out.println(e);
				}
			}
		}					
		return sessionformationList;
	}

	@Override
	public void create(SessionFormation sessionformation) {
		
		Connection con = null;		
		try{
		    con = ds.getConnection();
			PreparedStatement pstmt = con.prepareStatement("INSERT INTO SESSION_FORMATION (DEBUT,FIN,CODE_FORMATION,ID_LIEU) values (?,?,?,?)",new String[]{"ID"});
			pstmt.setDate(1, sessionformation.getDateDebut());
			pstmt.setDate(2, sessionformation.getDateFin());
			pstmt.setString(3, sessionformation.getFormationId());
			pstmt.setLong(4, sessionformation.getLieuId());
			pstmt.executeUpdate();
			ResultSet rs = pstmt.getGeneratedKeys();
			if(rs.next())
				sessionformation.setId(rs.getLong(1));
		}
		
		catch(Exception e){
			System.out.println("Exception methode create de la DAO session formation : " + e);
		}
		finally{
			if (con != null){
				
				try{
					con.close();
				}
				catch(Exception e)	{
					System.out.println(e);
				}
			}
		}		
	}

	@Override
	public void update(SessionFormation sessionformation) {
		Connection con = null;
		try{
			con = this.ds.getConnection();
			PreparedStatement pstmt = con.prepareStatement("UPDATE FORMATION SET DEBUT = ?, FIN = ?, CODE_FORMATION = ?, ID_LIEU = ? WHERE ID = ?");
			pstmt.setDate(1, sessionformation.getDateDebut());
			pstmt.setDate(2, sessionformation.getDateFin());
			pstmt.setString(3, sessionformation.getFormationId());
			pstmt.setLong(4, sessionformation.getLieuId());
			pstmt.executeUpdate();
		}
		catch(Exception e){
			System.out.println(e);
		}
		finally{
			if(con != null){
				try{
					con.close();
				}
				catch(Exception e){
					System.out.println(e);
				}
			}
		}	
	}

	@Override
	public void delete(SessionFormation sessionformation) {
		Connection con = null;	
		try{
		    con = ds.getConnection();		
			PreparedStatement pstmt = con.prepareStatement("DELETE SESSION_FORMATION WHERE ID = ?");
			pstmt.setLong(1, sessionformation.getId());
			pstmt.executeUpdate();
			pstmt = con.prepareStatement("DELETE CLIENT WHERE ID_SESSION = ?");
			pstmt.setLong(1, sessionformation.getId());
			pstmt.executeUpdate();
		}
		catch(Exception e){
			System.out.println(e);
		}
		finally{
			if (con != null){
				
				try{
					con.close();
				}
				catch(Exception e)	{
					System.out.println(e);
				}
			}
		}
	}	
}
